﻿
#region NameSpace
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using EB.Generics;
using System.Data.SqlClient;
using System.Data.Sql;
#endregion
/// <summary>
/// Summary description for Purchase
/// </summary>

namespace Purchase
{
    #region  Struct
    public struct SearchCriteria
   {
       string _setCriteria;

       public string SetCriteria
       {
           get { return _setCriteria; }
           set { _setCriteria = value; }
       }

       string _firstName;

       public string FirstName
       {
           get { return _firstName; }
           set { _firstName = value; }
       }

       string _productName;

       public string ProductName
       {
           get { return _productName; }
           set { _productName = value; }
       }

       string _dateofCreation;

       public string DateofCreation
       {
           get { return _dateofCreation; }
           set { _dateofCreation = value; }
       }
       string _dateofCreation1;

       public string DateofCreation1
       {
           get { return _dateofCreation1; }
           set { _dateofCreation1 = value; }
       }
       string _status;

       public string DateStatus
       {
           get { return _status; }
           set { _status = value; }
       }

       int _amount;

       public int Amount
       {
           get { return _amount; }
           set { _amount = value; }
       }
       int _reqStatus;
       public int Status
       {
           get { return _reqStatus; }
           set { _reqStatus = value; }
       }
   }
    #endregion

   
    public class Purchase
    {

        #region Constructor
        public Purchase()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #endregion

        #region Methods

        /// <summary>
        /// Get Materials
        /// </summary>
        /// <returns></returns>
        public DataSet GetMaterials()
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            DataSet materialDS;
            #endregion

            materialDS = new DataSet();
            query = "SELECT     ML_MaterialID, ML_Name,ML_Unit FROM         Materials";
            materialDS = commonClass.ExecuteQuery(query);
            return materialDS;
        }

       

        /// <summary>
        /// Get UnitPrice by Material Id
        /// </summary>
        /// <param name="materialId"></param>
        /// <returns></returns>

        public double GetUnitPriceByMaterialId(int materialId)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            double unitPrice=0;
            #endregion

            query = "SELECT     ML_Rate FROM         Materials where ML_MaterialID='"+ materialId +"'";
            DataSet resultDS = commonClass.ExecuteQuery(query);
            if (resultDS.Tables[0].Rows.Count > 0)
            {
                unitPrice =Convert.ToDouble(resultDS.Tables[0].Rows[0][0].ToString());

            }
            return unitPrice;

        }

        /// <summary>
        /// Get Products
        /// </summary>
        /// <returns></returns>
        public DataSet GetProducts()
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            double unitPrice = 0;
            #endregion
            query = "select ProductID,ProductName from ProductList";
            DataSet productDS = commonClass.ExecuteQuery(query);
            return productDS;
        }

        /// <summary>
        /// Get Item Details By ProductID
        /// </summary>
        /// <param name="productID"></param>
        /// <returns></returns>
        public DataSet GetMaterialDetailsByProductID(int productID)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            //query = "SELECT    Materials.ML_MaterialID, Materials.ML_Name, Materials.ML_Unit, Materials.ML_Rate FROM  ProductDetails INNER JOIN  ProductMaterialsMap ON ProductDetails.PD_ProductID = ProductMaterialsMap.PT_ProductID INNER JOIN Materials ON ProductMaterialsMap.PT_MaterialID = Materials.ML_MaterialID WHERE     (ProductMaterialsMap.PT_ProductID = '" + productID + "')";
            query = " SELECT     MaterialList.MaterialID, MaterialList.MaterialName, MaterialList.Unit, MaterialList.Price, MaterialList.UseDesc,MaterialList.Stock " +
                    " FROM         ProductMaterialsMap INNER JOIN ProductList ON ProductMaterialsMap.PT_ProductID = ProductList.ProductID INNER JOIN "+
                     " MaterialList ON ProductMaterialsMap.PT_MaterialID = MaterialList.MaterialID WHERE     (ProductMaterialsMap.PT_ProductID = '" + productID + "')"+
                    " SELECT     VR_VendorID, VR_VendorName FROM   VendorDetails";
            DataSet materialDS = commonClass.ExecuteQuery(query);
            return materialDS;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <param name="materialId"></param>
        /// <returns></returns>
        public int GetVendorNameByMaterialIDAndRequestId(int requestId,int materialId)
        {
            #region Fields
            string query = string.Empty;
            int vandorId = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "SELECT     PreferedVendor FROM BOMList WHERE     (RequestId = '" + requestId + "') AND (MaterialID = '" + materialId + "')";
            object vendorNo = commonClass.ExecuteScalar(query);
            if (vendorNo != "")
                vandorId = Convert.ToInt16(vendorNo);
            return vandorId;

        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public int GetPurchaseType(int requestId)
        {
             #region Fields
            string query = string.Empty;
            int requestTypeId = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "SELECT     PR_PurchaseType FROM         PurchaseRequestMaster where PR_RequestID='"+requestId+"'";
            object requestTypeNo = commonClass.ExecuteScalar(query);
            if (requestTypeNo != "")
                requestTypeId = Convert.ToInt16(requestTypeNo);
            return requestTypeId;
        }

        public int GetPurchaseTypeByOrder(int requestId)
        {
            #region Fields
            string query = string.Empty;
            int requestTypeId = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "SELECT     PurchaseType FROM         PurchaseOrderList where PurchaseOrderID='" + requestId + "'";
            object requestTypeNo = commonClass.ExecuteScalar(query);
            if (requestTypeNo != "")
                requestTypeId = Convert.ToInt16(requestTypeNo);
            return requestTypeId;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public DataSet GetPurchaseMasterDetailsByRequestId(int requestId)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     PR_ProductID, convert(varchar, PR_ActualLeadTime,103) as PR_ActualLeadTime, convert(varchar, PR_ExpectedLeadTime,103) as PR_ExpectedLeadTime , PR_ApproximateAmount, PR_DateTime, PR_ProductQuantity,Priority,Remarks" +
                    " FROM         PurchaseRequestMaster where PR_RequestID='"+requestId+"'";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;

        }

        public DataSet GetPurchaseOrderMasterDetails(int requestId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     PurchaseRequestMaster.PR_ProductID, CONVERT(varchar, PurchaseRequestMaster.PR_ActualLeadTime, 103) AS PR_ActualLeadTime, " +
                      " CONVERT(varchar, PurchaseRequestMaster.PR_ExpectedLeadTime, 103) AS PR_ExpectedLeadTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity,  " +
                      " PurchaseRequestMaster.Priority FROM         PurchaseRequestMaster INNER JOIN   PurchaseOrderList ON PurchaseRequestMaster.PR_RequestID = PurchaseOrderList.PurchaseRequestID " +
                      " where PurchaseOrderID='"+requestId+"'";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetProductionWisePurchasedItems(int userId)
        {

            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            //query = "SELECT     TOP (5) ProductDetails.PD_ProductName, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, "+
            //          " PurchaseRequestMaster.PR_ActualLeadTime, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, "+
            //         " PurchaseRequestMaster.PR_ProductID, isnull(UserDetails.UD_FirstName,'')+ ' ' +isnull(UserDetails.UD_MiddleName,'')+ ' ' +isnull(UserDetails.UD_LastName,'') as Requester, "+
            //          "InboxStatusList.StatusNote,PurchaseRequestMaster.PR_RequestID,  InboxStatusList.Status FROM         PurchaseRequestMaster INNER JOIN   ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID INNER JOIN "+
            //         " RequestList ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey INNER JOIN  UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID INNER JOIN "+
            //          " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) "+
            //        " ORDER BY PurchaseRequestMaster.PR_DateTime desc";
            query = "SELECT  DISTINCT    TOP (5) ProductList.ProductName, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, " +
                      " PurchaseRequestMaster.PR_ActualLeadTime, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, "+
                      " PurchaseRequestMaster.PR_ProductID, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_RequestID, "+
                      " InboxStatusList.Status,RequestList.RequestID FROM         ProductList INNER JOIN  PurchaseRequestMaster ON ProductList.ProductID = PurchaseRequestMaster.PR_ProductID LEFT OUTER JOIN " +
                      " UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN "+
                      " InboxStatusList ON RequestList.Status = InboxStatusList.Status ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey" +
                      " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and (RequestList.Status=1) and  RequestList.RequesterID='" + userId + "' ORDER BY PurchaseRequestMaster.PR_DateTime DESC";

            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetGeneralPurchasedItems(int userId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            //query="SELECT     TOP (5) PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, "+
            //       " PurchaseRequestMaster.PR_ActualLeadTime, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, "+
            //       " PurchaseRequestMaster.PR_ProductID, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '')  "+
            //          " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester, PurchaseRequestStatus.PurchaseRequestStatus as StatusNote, "+
            //       " PurchaseRequestMaster.PR_RequestID,  PurchaseRequestStatus.StatusId as Status FROM         PurchaseRequestStatus INNER JOIN "+
            //       " PurchaseRequestMaster ON PurchaseRequestStatus.StatusId = PurchaseRequestMaster.PurchaseRequestStatus LEFT OUTER JOIN "+
            //       " UserDetails INNER JOIN   RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey "+
            //       " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) ORDER BY PurchaseRequestMaster.PR_DateTime DESC";
            query = "SELECT     TOP (5) PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, "+
                    " PurchaseRequestMaster.PR_ActualLeadTime, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, "+
                    " PurchaseRequestMaster.PR_ProductID, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                    " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester, RequestListStatus.StatusNote, PurchaseRequestMaster.PR_RequestID, "+
                    " RequestListStatus.Status, RequestList.RequestID, PurchaseRequestMaster.Priority FROM         PurchaseRequestMaster INNER JOIN  RequestList ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey INNER JOIN "+
                    " UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID INNER JOIN  RequestListStatus ON RequestList.Status = RequestListStatus.Status "+
                    " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  RequestList.RequesterID='" + userId + "' and RequestList.Status=1 ORDER BY PurchaseRequestMaster.PR_DateTime DESC ";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }
        public bool UpdatePurchaseRequestStatus(int requestId, int statusId)
        {
            #region Fields
            bool status1;
            object identity = 0;
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "UPDATE    PurchaseRequestMaster SET  PurchaseRequestStatus ='" + statusId + "' where PR_RequestID='" + requestId + "'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status1 = true;
            else
                status1 = false;
            return status1;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public DataSet GetPrchasedItemsByRequestId(int requestId)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     BOMList.RequestId, BOMList.Quantity, PurchaseRequestMaster.PR_ProductQuantity, MaterialList.MaterialName, "+
                    " PurchaseRequestMaster.PR_ActualLeadTime, PurchaseRequestMaster.PR_ExpectedLeadTime, PurchaseRequestMaster.PR_ProductID, "+
                    " BOMList.MaterialID AS PR_MaterialID, MaterialList.MaterialID, MaterialList.Unit, MaterialList.Price, BOMList.PreferedVendor "+
                    " FROM         PurchaseRequestMaster INNER JOIN  BOMList ON PurchaseRequestMaster.PR_RequestID = BOMList.RequestId INNER JOIN "+
                    " MaterialList ON BOMList.MaterialID = MaterialList.MaterialID INNER JOIN  ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID    where  BOMList.RequestId='" + requestId + "'";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }

        public DataSet GetPuchaseOrderedItemsByRequestId(int requestId)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query =" SELECT     BOMList.RequestId, BOMList.Quantity, ProductList.ProductName, PurchaseRequestMaster.PR_ProductQuantity, MaterialList.MaterialName, "+
                   " PurchaseRequestMaster.PR_ActualLeadTime, PurchaseRequestMaster.PR_ExpectedLeadTime, PurchaseRequestMaster.PR_ProductID, BOMList.MaterialID AS PR_MaterialID, MaterialList.MaterialID, MaterialList.Unit, MaterialList.Price, BOMList.PreferedVendor "+
                   " FROM         PurchaseRequestMaster INNER JOIN  BOMList ON PurchaseRequestMaster.PR_RequestID = BOMList.RequestId INNER JOIN "+
                   " MaterialList ON BOMList.MaterialID = MaterialList.MaterialID INNER JOIN  PurchaseOrderList ON PurchaseRequestMaster.PR_RequestID = PurchaseOrderList.PurchaseRequestID INNER JOIN "+
                   " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID WHERE     (PurchaseOrderList.PurchaseOrderID = '"+requestId+"')";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }
      
        /// <summary>
        /// 
        /// </summary>
        /// <param name="vendorId"></param>
        /// <returns></returns>
        public DataSet GetVendorDetailsById(int vendorId)
        {
              #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     VR_VendorName, VR_VendorAddress, VR_VendorPhone, VR_VendorEmailID " +
                    " FROM         VendorDetails where VR_VendorID='" + vendorId + "'";
            DataSet vendorDS = commonClass.ExecuteQuery(query);
            return vendorDS;
        }

       
        public DataTable test()
        {
            string query = string.Empty;
            DataTable dt = new DataTable();

            EB.Generics.Generics commonClass = new EB.Generics.Generics();
              SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
              sqlDataAdapter.SelectCommand.CommandText = "SELECT     UD_UserPersonalID, UD_FirstName, UD_DateOfBirth FROM         UserDetails";
              sqlDataAdapter.Fill(dt);
              return dt;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="vendorId"></param>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public DataTable GetVendorDetailsByProductId(int productId, int vendorId, int requestId)
        {
             #region Fields
            string query = string.Empty;
            DataTable dt = new DataTable();

            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            //sqlDataAdapter.SelectCommand.CommandText = "SELECT     MaterialList.MaterialID, MaterialList.MaterialName, MaterialList.Unit as UnitQty, MaterialList.Price as UnitPrice, MaterialList.UseDesc as Unit " +
            //        " FROM         ProductMaterialsMap INNER JOIN   ProductDetails ON ProductMaterialsMap.PT_ProductID = ProductDetails.PD_ProductID INNER JOIN " +
            //        " MaterialList ON ProductMaterialsMap.PT_MaterialID = MaterialList.MaterialID INNER JOIN  MaterialSuplierList ON MaterialList.MaterialID = MaterialSuplierList.MaterialId " +
            //            " WHERE     (ProductMaterialsMap.PT_ProductID = '" + productId + "') and (MaterialSuplierList.VendorId='" + vendorId + "')";

            sqlDataAdapter.SelectCommand.CommandText = "SELECT     MaterialList.MaterialID, MaterialList.MaterialName, MaterialList.Unit AS UnitQty, MaterialList.Price AS UnitPrice, MaterialList.UseDesc AS Unit"+
                                                      " FROM         ProductMaterialsMap INNER JOIN  MaterialList ON ProductMaterialsMap.PT_MaterialID = MaterialList.MaterialID INNER JOIN BOMList ON MaterialList.MaterialID = BOMList.MaterialID INNER JOIN "+
                                                     " ProductList ON ProductMaterialsMap.PT_ProductID = ProductList.ProductID "+
                                                     " WHERE     (BOMList.PreferedVendor ='" + vendorId + "') AND (BOMList.RequestId ='" + requestId + "') AND (ProductMaterialsMap.PT_ProductID = '" + productId + "')";

            sqlDataAdapter.Fill(dt);
            return dt;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="purchaseType"></param>
        /// <param name="approximateAmt"></param>
        /// <param name="expectedTime"></param>
        /// <param name="actualTime"></param>
        /// <param name="productID"></param>
        /// <param name="productQty"></param>
        /// <returns></returns>
        public object InsertProductionWisePurchaseMasterTable(int purchaseType, double approximateAmt, string expectedTime, string actualTime, int productID, int productQty, int priority,string remarks)
        {
            #region Fields
            object identity = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion

            query = "INSERT INTO PurchaseRequestMaster(PR_PurchaseType, PR_DateTime, PR_ApproximateAmount, PR_ExpectedLeadTime, PR_ActualLeadTime,PR_ProductID,PR_ProductQuantity,Priority,Remarks) " +
                " VALUES     ('" + purchaseType + "',getdate(),'" + approximateAmt + "','" + expectedTime + "','" + actualTime + "','" + productID + "','" + productQty + "','" + priority + "','"+remarks+"') Select Scope_Identity()";
            identity = commonClass.ExecuteScalar(query);
            return identity;
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="RequestId"></param>
        /// <param name="ProductName"></param>
        /// <param name="RequiredQuantity"></param>
        /// <param name="UnitPrice"></param>
        /// <param name="specification"></param>
        /// <returns></returns>
        public bool InsertGeneralPurchaseRequest(int RequestId, string ProductName, int RequiredQuantity, double UnitPrice,string specification)
        {
            #region Fields
            int count = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "INSERT INTO PurchaseRequestGeneral(RequestId, ProductName, RequiredQuantity, UnitPrice, Specification) " +
                    " VALUES     ('" + RequestId + "','" + ProductName + "','" + RequiredQuantity + "','" + UnitPrice + "','" + specification + "')";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="RequestId"></param>
        /// <param name="ProductName"></param>
        /// <param name="RequiredQuantity"></param>
        /// <param name="UnitPrice"></param>
        /// <param name="specification"></param>
        /// <returns></returns>
        public bool UpdateGeneralPurchaseRequest(int RequestId, string ProductName, int RequiredQuantity, double UnitPrice,string specification,int uniqId)
        {
              #region Fields
            int count = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "UPDATE    PurchaseRequestGeneral SET   ProductName ='" + ProductName + "', RequiredQuantity ='" + RequiredQuantity + "', UnitPrice ='" + UnitPrice + "', Specification ='" + specification + "' where RequestId='" + RequestId + "' and UniqID='"+uniqId+"'";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestUniqId"></param>
        /// <param name="productId"></param>
        /// <param name="materialId"></param>
        /// <param name="materialQuantity"></param>
        /// <param name="vendor"></param>
        /// <returns></returns>
        public bool InsertBOMList(int requestUniqId,int productId,int materialId,int materialQuantity,int vendor)
        {
             #region Fields
            int count = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "INSERT INTO BOMList(RequestId, ProductID, MaterialID, Quantity, PreferedVendor) " +
                    " VALUES     ('" + requestUniqId + "','" + productId + "','" + materialId + "','" + materialQuantity + "','" + vendor + "')";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="materialName"></param>
        /// <returns></returns>
        public object InsertMaterials(string materialName)
        {
              #region Fields
            object identity = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "INSERT INTO Materials (ML_Name)VALUES     ('" + materialName + "') Select Scope_Identity()";
            identity = commonClass.ExecuteScalar(query);
            return identity;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="materialId"></param>
        /// <returns></returns>
        public DataSet GetVendorDetails(int materialId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     MaterialList.MaterialName, VendorDetails.VR_VendorName, MaterialSuplierList.MaterialId, MaterialSuplierList.VendorID " +
                    " FROM         MaterialSuplierList INNER JOIN   MaterialList ON MaterialSuplierList.MaterialId = MaterialList.MaterialID INNER JOIN "+
                    " VendorDetails ON MaterialSuplierList.VendorID = VendorDetails.VR_VendorID WHERE     (MaterialSuplierList.MaterialId = '" + materialId + "')";
            DataSet pvendorDetailsDS = commonClass.ExecuteQuery(query);
            return pvendorDetailsDS;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetPurchaseRequest()
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     Materials.ML_Name, PurchaseRequest.PR_RequestQuantity, PurchaseRequest.PR_RequestDate, PurchaseRequest.PR_Priority FROM         PurchaseRequest INNER JOIN   Materials ON PurchaseRequest.PR_MaterialID = Materials.ML_MaterialID";
            DataSet purchaseMaterialDS = commonClass.ExecuteQuery(query);
            return purchaseMaterialDS;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool SavePurchaseRequestToInBox()
        {
              #region Fields
            string query = string.Empty;
            bool status;
            int count = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public bool DeleteBOMList(int requestId)
        {
                #region Fields
            string query = string.Empty;
            int count = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query="DELETE FROM BOMList where RequestId='"+requestId+"'";
            commonClass.ExecuteNonQuery(query);

            query = " DELETE FROM PurchaseRequestMaster where PurchaseRequestMaster.PR_RequestID='" + requestId + "'";
            commonClass.ExecuteNonQuery(query);
            return true;
           
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="purchaseType"></param>
        /// <param name="approximateAmt"></param>
        /// <param name="expectedTime"></param>
        /// <param name="actualTime"></param>
        /// <param name="productID"></param>
        /// <param name="productQty"></param>
        /// <param name="uniqId"></param>
        public void UpdateProductionWisePurchaseRequestMaster(int purchaseType, double approximateAmt,string expectedTime,string actualTime,int productID,int productQty,int uniqId,int priority)
        {
            #region Fields
            object identity = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            if (productID != 0)
            {
                query = "UPDATE    PurchaseRequestMaster SET  PR_PurchaseType ='" + purchaseType + "',  PR_ApproximateAmount ='" + approximateAmt + "', PR_ExpectedLeadTime ='" + expectedTime.ToString() + "', "
                + " PR_ActualLeadTime ='" + actualTime.ToString() + "', PR_ProductID ='" + productID + "',  PR_ProductQuantity ='" + productQty + "',Priority='" + priority + "' where PR_RequestID='" + uniqId + "' ";
            }
            else
            {
                query = "UPDATE    PurchaseRequestMaster SET  PR_PurchaseType ='" + purchaseType + "',  PR_ApproximateAmount ='" + approximateAmt + "', PR_ExpectedLeadTime ='" + expectedTime.ToString() + "', "
                        + " PR_ActualLeadTime ='" + actualTime.ToString() + "', PR_ProductID =Null,  PR_ProductQuantity =null ,Priority='" + priority + "' where PR_RequestID='" + uniqId + "' ";

            }
            int count= commonClass.ExecuteNonQuery(query);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <param name="productID"></param>
        /// <param name="materialID"></param>
        /// <param name="quantity"></param>
        /// <param name="preferedVendor"></param>
        /// <returns></returns>
        public bool UpdateBOMListByRequestId(int requestId, int productID, int materialID, int quantity, int preferedVendor)
        {
            #region Fields
            bool status;
            object identity = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "UPDATE    BOMList SET   ProductID ='" + productID + "', MaterialID ='" + materialID + "', Quantity ='" + quantity + "', PreferedVendor ='" + preferedVendor + "' where RequestId ='" + requestId + "' and MaterialID ='" + materialID + "'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="linkKey"></param>
        /// <param name="requestTypeId"></param>
        /// <param name="requesterId"></param>
        /// <returns></returns>
        public bool UpdateReQuestStatus(int linkKey, int requestTypeId, int requesterId)
        {
            
              #region Fields
            bool status;
            object identity = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "UPDATE    RequestList SET  RequesterID ='" + requesterId + "', Status =1, CreationTime =getdate() where RequestTypeID='"+requestTypeId+"' and LinkKey ='"+linkKey+"'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="linkKey"></param>
        /// <param name="requestTypeId"></param>
        /// <param name="requesterId"></param>
        /// <returns></returns>
        public bool InsertReQuestStatus(int linkKey, int requestTypeId, int requesterId)
        {
            #region Fields
            bool status;
            object identity = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            query = "INSERT INTO RequestList(RequesterID, Status, CreationTime, LinkKey, RequestTypeID) " +
                    " VALUES     ('" + requesterId + "', 1, GETDATE(),'" + linkKey + "','" + requestTypeId + "')";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetInBoxStatus()
        {
               #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     Status, StatusNote FROM         InboxStatusList";
            DataSet statusDS = commonClass.ExecuteQuery(query);
            return statusDS;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public bool SearchGeneralPurchaseRequest(SearchCriteria criteria, out DataSet result)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            result = new DataSet();

            if (criteria.FirstName != null && criteria.FirstName !="")
            {
                   sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                              " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status,RequestList.RequestID " +
                                                              " FROM UserDetails INNER JOIN RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                              " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  UserDetails.UD_FirstName " + "like '" + '%' + "' + '" + criteria.FirstName + "' + '" + '%' + "' order by RequestListStatus.Status"; 



                //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') " +
                //                                          " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestGeneral.ProductName,  " +
                //                                          " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice,InboxStatusList.Status FROM         UserDetails INNER JOIN    RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                //                                          " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status INNER JOIN   PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId " +
                //                                          "WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  UserDetails.UD_FirstName " +
                //                                           "like '" + '%' + "' + '" + criteria.FirstName + "' + '" + '%' + "' order by InboxStatusList.Status"; 
            }
            else if (criteria.ProductName != null && criteria.ProductName != "")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                            " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, PurchaseRequestGeneral.ProductName, "+
                                                            " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN   RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                                                            " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN  PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId INNER JOIN "+
                                                            " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  PurchaseRequestGeneral.ProductName " + "like '" + '%' + "' + '" + criteria.ProductName + "' + '" + '%' + "' order by RequestListStatus.Status";
            }
            else if (criteria.Status != 0)
            {
                                   sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                                              " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status,RequestList.RequestID " +
                                                                              " FROM  UserDetails INNER JOIN RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                                              " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  RequestList.Status='" + criteria.Status + "' order by RequestList.Status";

                //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') " +
                //                                          " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestGeneral.ProductName,  " +
                //                                          " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice,InboxStatusList.Status FROM         UserDetails INNER JOIN    RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                //                                          " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status INNER JOIN   PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId " +
                //                                          "WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  RequestList.RequestStatus='" + criteria.Status + "' order by InboxStatusList.Status";
            }
               else if (criteria.DateofCreation!= null && criteria.DateofCreation!="")
            {
                if (criteria.DateStatus == "Between")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                                " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status,RequestList.RequestID " +
                                                                " FROM UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                                " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) and convert(varchar,cast('" + criteria.DateofCreation1 + "' as datetime),103)"+
                                                                " ORDER BY RequestListStatus.Status ";

                //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') " +
                //                                          " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestGeneral.ProductName,  " +
                //                                          " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice,InboxStatusList.Status FROM         UserDetails INNER JOIN    RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                //                                          " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status INNER JOIN   PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId " +
                //                                          "WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103) between  convert(varchar,cast('"+criteria.DateofCreation+"' as datetime),103) and convert(varchar,cast('"+criteria.DateofCreation1+"' as datetime),103)";
                }
                else if (criteria.DateStatus == "<=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                                " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status,RequestList.RequestID " +
                                                                " FROM  UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                                " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)" +
                                                                 " ORDER BY RequestListStatus.Status ";

                                    //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') " +
                                    //                      " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestGeneral.ProductName,  " +
                                    //                      " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice,InboxStatusList.Status FROM         UserDetails INNER JOIN    RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                                    //                      " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status INNER JOIN   PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId " +
                                    //                      "WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) order by InboxStatusList.Status";


                }
                else if (criteria.DateStatus == ">=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                               " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status ,RequestList.RequestID" +
                                                               " FROM UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                               " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)" +
                                                               " ORDER BY RequestListStatus.Status ";

                                    //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') " +
                                    //                      " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestGeneral.ProductName,  " +
                                    //                      " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice,InboxStatusList.Status FROM         UserDetails INNER JOIN    RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                                    //                      " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status INNER JOIN   PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId " +
                                    //                      "WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) order by InboxStatusList.Status";
                }
                else
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                               " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount,PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status,RequestList.RequestID " +
                                                               " FROM  UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                               " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)" +
                                                               " ORDER BY RequestListStatus.Status ";


                    //                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') " +
                    //                                      " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestGeneral.ProductName,  " +
                    //                                      " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice,InboxStatusList.Status FROM         UserDetails INNER JOIN    RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                    //                                      " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status INNER JOIN   PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId " +
                    //                                      "WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) order by InboxStatusList.Status";
                    ////and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)= convert(varchar,cast('"+criteria.DateofCreation+"' as datetime),103)";

                }

            }
            else
            {
                   sqlDataAdapter.SelectCommand.CommandText = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                                                               " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status,RequestList.RequestID " +
                                                               " FROM         UserDetails INNER JOIN RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN   PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                                                               " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) "+
                                                              " ORDER BY RequestListStatus.Status ";
               }

             sqlDataAdapter.Fill(result);
            return true;


        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public bool SearchPurchaseRequest(SearchCriteria criteria, out DataSet result)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            result = new DataSet();

            if (criteria.FirstName != null && criteria.FirstName!="")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote,  "+
                      " PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                      " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN  RequestListStatus ON RequestList.Status = RequestListStatus.Status INNER JOIN "+
                      " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where PurchaseRequestMaster.PR_PurchaseType=1 and  UserDetails.UD_FirstName " + "like '" + '%' + "' + '" + criteria.FirstName + "' + '" + '%' + "'";
            }
            else if (criteria.ProductName != null && criteria.ProductName!="")
            {
                sqlDataAdapter.SelectCommand.CommandText="SELECT     ProductList.ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote,  "+
                      " PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                      " PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN  RequestListStatus ON RequestList.Status = RequestListStatus.Status INNER JOIN "+
                      " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where PurchaseRequestMaster.PR_PurchaseType=1 and  ProductList.ProductName like '" + '%' + "' + '" + criteria.ProductName + "' + '" + '%' + "'";
                //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                //      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status " +
                //        " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                //      " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                //      " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status where PurchaseRequestMaster.PR_PurchaseType=1 and  ProductDetails.PD_ProductName like '" + '%' + "' + '" + criteria.ProductName + "' + '" + '%' + "'";

            }
            else if (criteria.Status != 0)
            {
                sqlDataAdapter.SelectCommand.CommandText="SELECT     ProductList.ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '')  + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, "+
                      " PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, RequestListStatus.Status, PurchaseRequestMaster.PR_ProductQuantity ,RequestList.RequestID FROM         UserDetails INNER JOIN " +
                      " RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN  RequestListStatus ON RequestList.Status = RequestListStatus.Status INNER JOIN "+
                      " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) AND (RequestList.Status ='"+ criteria.Status +"')";
                //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                //      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote ,RequestList.RequestStatus, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status" +
                //        " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                //      " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                //      " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status where PurchaseRequestMaster.PR_PurchaseType=1 and RequestList.RequestStatus='"+ criteria.Status +"' ";
            }
            else if (criteria.DateofCreation!= null && criteria.DateofCreation!="")
            {
                if (criteria.DateStatus == "Between")
                {
                    sqlDataAdapter.SelectCommand.CommandText="SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, "+
                      " PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                      " PurchaseRequestMaster INNER JOIN ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                      " RequestListStatus ON RequestList.Status = RequestListStatus.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) "+
                      " and convert(varchar,PurchaseRequestMaster.PR_DateTime,103) between  convert(varchar,cast('"+criteria.DateofCreation+"' as datetime),103) and convert(varchar,cast('"+criteria.DateofCreation1+"' as datetime),103)";
                    //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                    // " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status " +
                    //   " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                    // " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                    // " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103) between  convert(varchar,cast('"+criteria.DateofCreation+"' as datetime),103) and convert(varchar,cast('10/10/2009' as datetime),103)";

                }
                else if (criteria.DateStatus == "<=")
                {
                    //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                    // " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status" +
                    //   " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                    // " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                    // " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                    sqlDataAdapter.SelectCommand.CommandText="SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, "+
                      " PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN " +
                      " PurchaseRequestMaster INNER JOIN  ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON  RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN  RequestListStatus ON RequestList.Status = RequestListStatus.Status "+
                      " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                }
                else if (criteria.DateStatus == ">=")
                {
                    //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                    // " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status " +
                    //   " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                    // " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                    // " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                    sqlDataAdapter.SelectCommand.CommandText="SELECT     ProductList.ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote,  "+
                      " PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                      " RequestListStatus ON RequestList.Status = RequestListStatus.Status INNER JOIN       ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID  WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1)and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";
                }
                else
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, "+
                      " PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status,RequestList.RequestID FROM         UserDetails INNER JOIN RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                      " RequestListStatus ON RequestList.Status = RequestListStatus.Status INNER JOIN  ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                    //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                    // " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status " +
                    //   " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                    // " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                    // " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) and convert(varchar,PurchaseRequestMaster.PR_DateTime,103)= convert(varchar,cast('"+criteria.DateofCreation+"' as datetime),103)";

                }

            }
            else
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, "+
                      " PurchaseRequestMaster.PR_ProductQuantity, RequestListStatus.Status ,RequestList.RequestID FROM         UserDetails INNER JOIN RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                      " RequestListStatus ON RequestList.Status = RequestListStatus.Status INNER JOIN  ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID "+
                                           " where (PurchaseRequestMaster.PR_PurchaseType = 1) order by RequestListStatus.Status ";

                //sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') " +
                //      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote, PurchaseRequestMaster.PR_ProductQuantity, InboxStatusList.Status " +
                //        " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster INNER JOIN " +
                //      " ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                //      " InboxStatusList ON RequestList.RequestStatus = InboxStatusList.Status WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) order by InboxStatusList.Status";

            }
            sqlDataAdapter.Fill(result);
            return true;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public DataSet GetVendorByRequestId(int requestId)
        {
            
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "select distinct PreferedVendor from BOMList where RequestId='"+requestId+"'";
            DataSet requestDS = commonClass.ExecuteQuery(query);
            return requestDS;
        }

        public DataSet GetPriority()
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     PriorityId, Priority FROM         Priority";
            DataSet priorityDS = commonClass.ExecuteQuery(query);
            return priorityDS;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetUsers()
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     UD_UserPersonalID,isnull(UD_FirstName,'')+' '+isnull(UD_MiddleName,'')+' '+isnull(UD_LastName,'') as UserName FROM         UserDetails";
            DataSet userDS = commonClass.ExecuteQuery(query);
            return userDS;
        }
        public DataSet GetUsers(int groupId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
           // query = "select *from SendForApproval where  UR_UserGroupID='" + groupId + "'";
            query = "SELECT     UserDetails.UD_UserPersonalID, isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName,'') as UserName " +
                   " FROM         UserDetails INNER JOIN   UserGroupMap ON UserDetails.UD_UserPersonalID = UserGroupMap.UR_UserID INNER JOIN " +
                  " UserGroup ON UserGroupMap.UR_UserGroupID = UserGroup.US_GroupID where UserGroup.US_GroupID='" + groupId + "'";
            DataSet userDS = commonClass.ExecuteQuery(query);
            return userDS;
        }
       

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public DataSet GetGeneralPurchasedDetailsByRequestId(int requestId)
        {
              #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     ProductName, RequiredQuantity, UnitPrice, Specification,UniqID FROM         PurchaseRequestGeneral where RequestId='"+requestId+"'";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }
        public DataSet GetGeneralPurchaseOrderedDetailsByRequestId(int requestId)
        {
              #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     PurchaseRequestGeneral.ProductName, PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice, " +
                    " PurchaseRequestGeneral.Specification, PurchaseRequestGeneral.RequestId  FROM         PurchaseOrderList INNER JOIN PurchaseOrderItemsGeneral ON PurchaseOrderList.PurchaseOrderID = PurchaseOrderItemsGeneral.PurchaseOrderID INNER JOIN " +
                    " PurchaseRequestGeneral ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestGeneral.RequestId where  PurchaseOrderList.PurchaseOrderID='" + requestId + "' ";

            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetPurchaseRequestApprovedInProduction(int userID)
        {
               #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

           query="SELECT     PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, PurchaseRequestMaster.PR_ActualLeadTime, "+
                     " PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, PurchaseRequestMaster.PR_ProductID, "+
                     " ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester, "+
                     " PurchaseRequestMaster.PR_RequestID, RequestList.Status AS Expr1, RequestList.RequesterID, PurchaseOrderList.Status AS Expr2, "+
                     " ProductList.ProductName AS PD_ProductName, RequestListStatus.Status, RequestListStatus.StatusNote"+
" FROM         PurchaseRequestMaster INNER JOIN  RequestList ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey INNER JOIN "+
                    " UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID INNER JOIN    ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID INNER JOIN "+
                    " RequestListStatus ON RequestList.Status = RequestListStatus.Status LEFT OUTER JOIN    PurchaseOrderList ON PurchaseRequestMaster.PR_RequestID = PurchaseOrderList.PurchaseRequestID "+
" WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) AND (RequestList.RequesterID = '" + userID + "')  AND (RequestListStatus.Status = 5) AND (PurchaseOrderList.Status IS NULL) " +
" ORDER BY PurchaseRequestMaster.PR_DateTime DESC";


            //query = "SELECT     PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, PurchaseRequestMaster.PR_ActualLeadTime, "+
            //          " PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, PurchaseRequestMaster.PR_ProductID, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester,  "+
            //          " InboxStatusList.StatusNote, PurchaseRequestMaster.PR_RequestID, InboxStatusList.Status, RequestList.Status AS Expr1, RequestList.RequesterID, "+
            //          " PurchaseOrderList.Status AS Expr2, ProductList.ProductName AS PD_ProductName FROM         PurchaseRequestMaster INNER JOIN  RequestList ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey INNER JOIN "+
            //          " UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID INNER JOIN InboxStatusList ON RequestList.Status = InboxStatusList.Status INNER JOIN "+
            //          " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID LEFT OUTER JOIN PurchaseOrderList ON PurchaseRequestMaster.PR_RequestID = PurchaseOrderList.PurchaseRequestID "+
            //          " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1) AND (InboxStatusList.Status = 4) AND (RequestList.RequesterID = 23) AND (PurchaseOrderList.Status IS NULL) "+
            //          " ORDER BY PurchaseRequestMaster.PR_DateTime DESC";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <param name="vendorId"></param>
        /// <param name="purchaseType"></param>
        /// <param name="createdBy"></param>
        /// <returns></returns>
        public int InsertPurchaseOrder(int requestId,int purchaseType,int createdBy)
        {
            #region Fields
            int purchaseOredrId = 0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
                query = "INSERT INTO PurchaseOrderList(PurchaseRequestID, PurchaseType, DateOfCreation, CreatedBy, Status, " +
                " DateOfDelivery, LeadTime, ExpectedLeadTime)VALUES     ('" + requestId + "','" + purchaseType + "',getdate(),'" + createdBy + "',1,getdate(),getdate(),getdate()) Select Scope_Identity()";
            //else
            //    query = "INSERT INTO PurchaseOrderList(PurchaseRequestID, VendorID, PurchaseType, DateOfCreation, CreatedBy, Status, " +
            //        " DateOfDelivery, LeadTime, ExpectedLeadTime)VALUES     ('" + requestId + "','" + purchaseType + "',getdate(),'" + createdBy + "',1,getdate(),getdate(),getdate()) Select Scope_Identity()";

            object identity = commonClass.ExecuteScalar(query);
            if (identity != null)
                purchaseOredrId = Convert.ToInt16(identity);
            return purchaseOredrId;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool InsertPurchaseOrderBOMList(int purchaseOrderId,int materialId,string materialName,int quantity)
        {
             #region Fields
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            if(materialId!=0)
            query = "INSERT INTO PurchaseOrderItemsBOM(PurchaseOrderID, MaterialID, MaterialName, Qty)"+
                " VALUES     ('"+purchaseOrderId+"','"+materialId+"','"+materialName+"','"+quantity+"')";
            else
                query = "INSERT INTO PurchaseOrderItemsBOM(PurchaseOrderID, MaterialID, MaterialName, Qty)" +
                 " VALUES     ('" + purchaseOrderId + "',NULL,'" + materialName + "','" + quantity + "')";

            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        public bool InsertPurchaseOrderGeneral(int purchaseOrderId, int materialId, string materialName, int quantity)
        {
             #region Fields
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            #endregion
            if (materialId != 0)
                query = "INSERT INTO PurchaseOrderItemsGeneral(MaterialID, MaterialName, Qty, PurchaseOrderID) " +
                        " VALUES     ('" + materialId + "','"+materialName+"','"+quantity+"','"+purchaseOrderId+"')";
            else
                query = "INSERT INTO PurchaseOrderItemsGeneral(MaterialID, MaterialName, Qty, PurchaseOrderID) " +
        " VALUES     (NULL,'" + materialName + "','" + quantity + "','" + purchaseOrderId + "')";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <returns></returns>
        public DataSet GetVendorCountByRequestId(int requestId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     BOMList.PreferedVendor,Count(BOMList.PreferedVendor) as vendorCount FROM         PurchaseRequestMaster INNER JOIN " +
                      " BOMList ON PurchaseRequestMaster.PR_RequestID = BOMList.RequestId where  BOMList.RequestId='"+requestId+"' group by BOMList.PreferedVendor ";
            DataSet vendorDS = commonClass.ExecuteQuery(query);
            return vendorDS;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestId"></param>
        /// <param name="vandorId"></param>
        /// <returns></returns>
        public DataSet GetMaterialDSByVendorID(int requestId, int vandorId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     MaterialList.MaterialName, BOMList.BOMVersion, BOMList.MaterialID, BOMList.Quantity, BOMList.PreferedVendor, ProductList.ProductName, BOMList.RequestId "+
                    " FROM         PurchaseRequestMaster INNER JOIN BOMList ON PurchaseRequestMaster.PR_RequestID = BOMList.RequestId INNER JOIN  MaterialList ON BOMList.MaterialID = MaterialList.MaterialID INNER JOIN "+
                    " ProductList ON BOMList.ProductID = ProductList.ProductID WHERE   BOMList.PreferedVendor='" + vandorId + "' AND BOMList.RequestId='" + requestId + "' ORDER BY BOMList.PreferedVendor ";
            DataSet materialDS = commonClass.ExecuteQuery(query);
            return materialDS;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetGeneralPurchasedMaterials()
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            //query = "SELECT     PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_ExpectedLeadTime, PurchaseRequestMaster.PR_ActualLeadTime, "+
            //          " PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, PurchaseRequestMaster.PR_ProductID, "+
            //          " ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester, "+
            //          " InboxStatusList.StatusNote, PurchaseRequestMaster.PR_RequestID, InboxStatusList.Status, PurchaseOrderList.Status AS OrderStatus, "+
            //          " RequestList.Status FROM         PurchaseRequestMaster INNER JOIN   RequestList ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey INNER JOIN "+
            //          " UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID INNER JOIN  InboxStatusList ON RequestList.Status = InboxStatusList.Status LEFT OUTER JOIN "+
            //          " PurchaseOrderList ON PurchaseRequestMaster.PR_RequestID = PurchaseOrderList.PurchaseRequestID WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) AND (PurchaseOrderList.Status IS NULL) and RequestList.Status=5 "+
            //          " ORDER BY PurchaseRequestMaster.PR_DateTime DESC";

            query="SELECT     PurchaseRequestMaster.PR_ApproximateAmount, convert(varchar,PurchaseRequestMaster.PR_ExpectedLeadTime,103) as PR_ExpectedLeadTime, "+
                   " convert(varchar,PurchaseRequestMaster.PR_ActualLeadTime, 103) as PR_ActualLeadTime,PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ProductQuantity, PurchaseRequestMaster.PR_ProductID,  "+
                   " ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') AS Requester, RequestListStatus.StatusNote, PurchaseRequestMaster.PR_RequestID, RequestListStatus.Status, PurchaseOrderList.Status AS OrderStatus "+
                   " FROM         PurchaseRequestMaster INNER JOIN  RequestList ON PurchaseRequestMaster.PR_RequestID = RequestList.LinkKey INNER JOIN  UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID INNER JOIN "+
                   " RequestListStatus ON RequestList.Status = RequestListStatus.Status LEFT OUTER JOIN  PurchaseOrderList ON PurchaseRequestMaster.PR_RequestID = PurchaseOrderList.PurchaseRequestID "+
                   " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) AND (PurchaseOrderList.Status IS NULL) AND (RequestList.Status = 5) ORDER BY PurchaseRequestMaster.PR_DateTime DESC";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }

        public DataSet GetProductionWiseSearch()
        {
              #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     ProductDetails.PD_ProductName, ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') "+
                      " + ' ' + ISNULL(UserDetails.UD_LastName, '') AS RequesterName, PurchaseRequestMaster.PR_DateTime, "+
                      " PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, InboxStatusList.StatusNote,  PurchaseRequestMaster.PR_ProductQuantity "+
                      " FROM         UserDetails INNER JOIN  RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN "+
                      " PurchaseRequestMaster INNER JOIN   ProductDetails ON PurchaseRequestMaster.PR_ProductID = ProductDetails.PD_ProductID ON  "+
                      " RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN  InboxStatusList ON RequestList.Status = InboxStatusList.Status "+
                      " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 1)";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }
        public DataSet GetTest(int requestId)
        {
              #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     MaterialList.MaterialName, MaterialList.ComponentID, MaterialList.UseDesc, MaterialList.Unit, MaterialList.Price,  "+
                    " BOMList.RequestId, BOMList.Quantity, BOMList.PreferedVendor FROM         BOMList INNER JOIN   MaterialList ON BOMList.MaterialID = MaterialList.MaterialID "+
                    " where BOMList.RequestId='"+requestId+"'";
                 DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }

        public DataSet GetMaterialDetailsByRequestId(int requestId)
        {
               #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT     ISNULL(UserDetails.UD_FirstName, '') + ' ' + ISNULL(UserDetails.UD_MiddleName, '') + ' ' + ISNULL(UserDetails.UD_LastName, '') "+
                      " AS RequesterName, PurchaseRequestMaster.PR_DateTime, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseRequestMaster.PR_RequestID, RequestListStatus.StatusNote, PurchaseRequestGeneral.ProductName,  "+
                      " PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice, RequestListStatus.Status, PurchaseRequestGeneral.RequestId FROM         UserDetails INNER JOIN "+
                      " RequestList ON UserDetails.UD_UserPersonalID = RequestList.RequesterID INNER JOIN  PurchaseRequestMaster ON RequestList.LinkKey = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                      " PurchaseRequestGeneral ON PurchaseRequestMaster.PR_RequestID = PurchaseRequestGeneral.RequestId INNER JOIN  RequestListStatus ON RequestList.Status = RequestListStatus.Status "+
                       " WHERE     (PurchaseRequestMaster.PR_PurchaseType = 2) and  PurchaseRequestGeneral.RequestId='"+requestId+"'";
            DataSet purchaseDS = commonClass.ExecuteQuery(query);
            return purchaseDS;
        }

        public DataSet AutoCompleteUserName(string toSearch, int count)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            query = "SELECT  top  '"+count+"'   UD_FirstName FROM         UserDetails where UD_FirstName " +
                        "like '" + toSearch + "%'";
            DataSet userNameDs = commonClass.ExecuteQuery(query);
            return userNameDs;

        }

        public bool UpdateStepStatus(int stepStatus,int requestStatus,int recordNo)
        {
            
            #region Fields
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            int count = 0;
            bool status;
            #endregion
            query = "UPDATE    RequestList SET StepStatus ='" + stepStatus + "', Status ='" + requestStatus + "'  where RequestID='" + recordNo + "'";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }
        #endregion
    }
}